import pymysql
import traceback


# 建立一个简单的数据库操作类
class SimpleMySqlClass:
    # 函数要对数据库进行连接，并建立一个游标为操作数据库做准备
    def __init__(self, host, db_name, user, password):
        self.host = host
        self.db_name = db_name
        self.user = user
        self.password = password
        # self.port = port
        # 调用函数create_connection()对数据库进行连接
        self.connection = self.create_connection()
        # 调用函数create_cursor()生成一个游标，为操作数据库做好准备
        self.cursor = self.create_cursor()

    # 建立一个数据库连接
    def create_connection(self):
        # pymysql在1.0以后的版本需要这样写
        self.connection = pymysql.connect(
            host=self.host,
            database=self.db_name,
            user=self.user,
            password=self.password,
        )
        return self.connection

    # 建立一个游标
    def create_cursor(self):
        # 通过数据库连接建立一个操作游标
        self.cursor = self.connection.cursor()
        return self.cursor

    def get_cursor(self):
        return self.cursor

    # 执行查询
    def query_sql(self, sql):
        try:
            # 执行查询语句
            self.cursor.execute(sql)
            # 利用游标的fetchall()函数取得查询到的所有记录
            data = self.cursor.fetchall()
            # 返回记录
            return data
        except Exception:
            # 发生错误时返回有错误的SQL语句
            raise Exception("执行的SQL语句： " + sql + ",出现异常，请检查")

    # 执行查询
    def get_all(self, sql):
        try:
            # 执行查询语句
            self.cursor.execute(sql)
            # 利用游标的fetchall()函数取得查询到的所有记录
            data = self.cursor.fetchall()
            # 返回记录
            return data
        except Exception:
            # 发生错误时返回有错误的SQL语句
            raise Exception("执行的SQL语句： " + sql + ",出现异常，请检查")

    # 执行查询
    def get_one(self, sql):
        try:
            # 执行查询语句
            self.cursor.execute(sql)
            # 利用游标的fetchall()函数取得查询到的所有记录
            data = self.cursor.fetchone()
            # 返回记录
            return data
        except Exception:
            # 发生错误时返回有错误的SQL语句
            raise Exception("执行的SQL语句： " + sql + ",出现异常，请检查")

    # 执行增、删、改相关SQL语句
    def execute(self, sql):
        try:
            # 执行增、删、改SQL语句时，返回的值是操作结果影响的记录数
            re = self.cursor.execute(sql)
            # 提交执行事务
            self.connection.commit()
            return re
        except Exception as e:
            # 发生错误回滚事务
            self.connection.rollback()
            print("发生异常：", str(e))
            # 返回-1提示执行出错
            return -1

    def executeMany(self, sql, args):
        try:
            re = self.cursor.executemany(sql, args)
            self.connection.commit()
            return re

        except Exception as e:
            # 打印完整的异常信息
            self.connection.rollback()
            print("发生异常：", str(e))
            traceback.print_exc()
            return -1

    #     # 列出数据库中的表
    def list_tables(self):
        sql_1 = "show tables"
        try:
            self.cursor.execute(sql_1)
            table_list = self.cursor.fetchall()
            return table_list
        except Exception:
            raise Exception("执行的SQL语句：" + sql_1 + ",出现异常，请检查！")

    def check_table(self, table_name):
        sql_1 = f"show tables like '%{table_name}%'"
        try:
            self.cursor.execute(sql_1)
            table_list = self.cursor.fetchall()
            return table_list
        except Exception:
            raise Exception("执行的SQL语句：" + sql_1 + ",出现异常，请检查！")

    def insert(self, table, data):
        columns = ", ".join(data.keys())
        placeholders = ", ".join(["%s"] * len(data))
        sql = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        args = (tuple(data.values()),)
        return self.executeMany(sql, args)

    def insertIgnore(self, table, data):
        columns = ", ".join(data.keys())
        placeholders = ", ".join(["%s"] * len(data))
        sql = f"INSERT ignore INTO {table} ({columns}) VALUES ({placeholders})"
        args = (tuple(data.values()),)
        return self.executeMany(sql, args)

    def update(self, table, data, condition):
        set_values = ", ".join([f"{key} = '{value}'" for key, value in data.items()])
        sql = f"UPDATE {table} SET {set_values} WHERE {condition}"
        print("update sql = ", sql)
        return self.execute(sql)

    def delete(self, table, condition):
        sql = f"DELETE FROM {table} WHERE {condition}"
        return self.execute(sql)

    def count(self, table_name, condition):
        sql = f"select count(*) from {table_name}"
        if condition and len(condition) > 0:
            sql = sql + " where " + condition
        rs = self.get_one(sql)
        return rs[0]

    # 关闭连接，执行完相关的数据库操作，及时关闭连接是良好的编程习惯
    def close(self):
        self.connection.close()
